| 数据库 联合索引 vs 多个单列索引 （公司大型项目实际碰到的问题） | 您所在的位置：网站首页 › oracle 添加索引字段 › 数据库 联合索引 vs 多个单列索引 （公司大型项目实际碰到的问题） |
|
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。 本文链接：https://blog.csdn.net/q258523454/article/details/114933096 Oracle / MySQL都可以参考! 目录： Oracle 19c 联合索引 vs 多个列索引 环境准备 单列索引（多个） 联合索引 结论 Oracle 19c 联合索引 vs 多个列索引我们在创建索引的时候。到底是 选择联合索引还是选择多个单列索引？ 环境准备数据库oracle 19c 100万条随机数据 若只看结论，直接滑到最后 单列索引（多个）建表 （多个单列索引测试）： CREATE TABLE "TEST"."STUDENT" ( "ID" NUMBER(10,0) NOT NULL ENABLE, "NAME" VARCHAR2(10) NOT NULL ENABLE, "NAME2" CHAR(10) DEFAULT 'default', "AGE" NUMBER(4,0) NOT NULL ENABLE ) ; COMMENT ON COLUMN "TEST"."STUDENT"."NAME" IS '姓名'; COMMENT ON COLUMN "TEST"."STUDENT"."NAME2" IS '姓名2'; COMMENT ON COLUMN "TEST"."STUDENT"."AGE" IS '年龄'; CREATE UNIQUE INDEX STUDENT_ID_IDX ON STUDENT(ID); CREATE UNIQUE INDEX STUDENT_AGE_IDX ON STUDENT(AGE);写脚本插入数据。除了故意让有几个id字段重复之外，其他的全部随机 总共插入了接近100万条数据。（实际数据80万) 查看数据量 // 数据量 SELECT count(*) FROM student; COUNT(*)| --------| 802610| ID |COUNT(*)| -----|--------| 56789| 20004| 12345| 1106| 67072| 63|为了测试，故意准备了三个占比不同的id 首先测试 id=56789 SELECT count(*) FROM STUDENT WHERE id=56789; COUNT(*)| --------| 20004| SELECT count(*) FROM STUDENT WHERE age=907; COUNT(*)| --------| 71| SELECT count(*) FROM STUDENT WHERE id=56789 AND age='907' COUNT(*)| --------| 1| explain plan FOR SELECT * FROM STUDENT WHERE id=56789 AND age='907' ; SELECT * FROM TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT | -------------------------------------------------------------------------------------------------------| Plan hash value: 3916273152 | | -------------------------------------------------------------------------------------------------------| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time || -------------------------------------------------------------------------------------------------------| | 0 | SELECT STATEMENT | | 2 | 48 | 53 (2)| 00:00:01 || | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| STUDENT | 2 | 48 | 53 (2)| 00:00:01 || | 2 | BITMAP CONVERSION TO ROWIDS | | | | | || | 3 | BITMAP AND | | | | | || | 4 | BITMAP CONVERSION FROM ROWIDS | | | | | || |* 5 | INDEX RANGE SCAN | STUDENT_AGE_IDX | 80 | | 3 (0)| 00:00:01 || | 6 | BITMAP CONVERSION FROM ROWIDS | | | | | || |* 7 | INDEX RANGE SCAN | STUDENT_ID_IDX | 80 | | 49 (0)| 00:00:01 || -------------------------------------------------------------------------------------------------------| | Predicate Information (identified by operation id): | --------------------------------------------------- |我们可以看到 同时用到了 id 和 age 两个索引 继续测试 id=123456 SELECT count(*) FROM STUDENT WHERE id=12345; COUNT(*)| --------| 1106| SELECT count(*) FROM STUDENT WHERE age=7777; COUNT(*)| --------| 65| SELECT count(*) FROM STUDENT WHERE id=12345 AND age='7777' ; COUNT(*)| --------| 2| explain plan FOR SELECT * FROM STUDENT WHERE id=12345 AND age='7777' ; SELECT * FROM TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT | ------------------------------------------------------------------------------------------------------| Plan hash value: 2814013459 | | ------------------------------------------------------------------------------------------------------| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time || ------------------------------------------------------------------------------------------------------| | 0 | SELECT STATEMENT | | 1 | 24 | 8 (0)| 00:00:01 || |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| STUDENT | 1 | 24 | 8 (0)| 00:00:01 || |* 2 | INDEX RANGE SCAN | STUDENT_ID_IDX | 6 | | 3 (0)| 00:00:01 || ------------------------------------------------------------------------------------------------------| | Predicate Information (identified by operation id): | --------------------------------------------------- | | 1 - filter("AGE"=7777) | 2 - access("ID"=12345)可以看到只用到了 id 索引。 再测试一下 id=67072，结果一样。只用到了一个索引字段。 可以看出： 多个单列索引同时使用的时候 可能只用到一个，也可能同时用到多个。 什么时候才会同时生效呢？ 这个取决于数据库优化器，以上面的第一个id=56789查询结果为例，数据库认为 id=56789 本身就有2万条，而age=907的才71条。 那么数据库优化器选择先用age的索引,然后再用id的索引。 至于为什么后面两个 id=12345 和 id=67072 为什么不同时走2个索引，可能是因为数据库优化器认为id本身就能筛选和定位到很小范围，可能没必要走第二个索引。 联合索引这个就是对对上面的单个索引同时创建 (id,age)， 相当于创建了(id)+ (id,age)两个索引，这个不测试了。 结论：索引的目的:为了查询 禁止建索引的字段: 1.区分度不大的字段, eg: 性别 2.大数据类型的字段, eg: text数据类型 3.不常作查询条件的字段 创建联合索引还是多个单列索引? 目前版本的数据库大都是支持 索引合并功能的, 即查询可能会同时使用多个单列索引 1.如果 a,b,c 经常作为单独的查询条件, 则可考虑分别为 a,b,c 创建单个索引 2.如果 a,b,c 经常按 a,b,c 联合形式查询, 则创建组合索引 (a,b,c), 这里包含索引: (a),(a,b),(a,b,c),满足最左匹配原则 3.如果 a,b,c 经常按 a,b联合形式查询 和 单独c查询 则可考虑创建组合索引 (a,b)和 单独索引(c) 这样会多一个索引空间, 根据业务情况看是否有必要 总结: 根据实际业务情况 优先考虑能否创建联合索引,其次再考虑多个单列索引 原因: 1.过多的索引会消耗物理空间 2.过多的索引会影响增删改的维护速度 3.不能完全依赖mysql数据库index merge或者Oracle的优化器, 这个是由数据库来决定,不能保证每次都触发索引优化 参考文献： 17 Optimizer Hints Understanding Oracle INDEX-MERGE Joins |
| CopyRight 2018-2019 实验室设备网 版权所有 |